package com.sys.service;

import com.sys.GlobalVariable;
import com.sys.entity.Card;
import com.sys.entity.Commodity;
import com.sys.entity.Customer;
import com.sys.exception.DuplicateCardException;
import com.sys.utils.DbUtil4;
import com.sys.utils.DbUtil4Factory;

import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class CommodityService {
    public List<Map<String, Object>> showList(int uid, String cname, String batch,String expiretime_warn,String inventory_warn) throws Exception {
        DbUtil4 db = null;
        List<Map<String, Object>> list = null;
        String sql = "SELECT id,cname,batch,expiretime,purchaseprice,price,inventory,create_time,modify_time,isdel FROM t_commodity where isdel = 0 and uid = ?";
        List paramList = new ArrayList();
        paramList.add(uid);
        if(cname!=null && !"".equals(cname)){
            sql += " and cname like ?";
            paramList.add(cname+"%");
        }
        if(batch!=null && !"".equals(batch)){
            sql += " and batch like ?";
            paramList.add("%"+batch+"%");
        }
        if("yes".equals(expiretime_warn)){
            sql += " and TimeStampDiff(day,CURDATE(),expiretime)<30 ";
        }
        if("yes".equals(inventory_warn)){
            sql += " and inventory<100 ";
        }
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, paramList.toArray() , Commodity.class);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }

    public void add(String cname, String batch,String expiretime,String purchaseprice,String price,String inventory,Integer uid) throws Exception {
        DbUtil4 db = null;
        String selectSQL = "select count(1) as num from t_commodity where cname = ? and batch=?  and expiretime=? and purchaseprice = ? and price =? ";
        String sql = "INSERT INTO t_commodity (cname,batch,expiretime,purchaseprice,price,inventory,uid) VALUES (?,?,?,?,?,?,?)";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            List<Map<String, Object>> list = db.query(selectSQL, new Object[]{cname,batch,expiretime,purchaseprice,price});
            // 由于查询的是 select count(1) ，所以一定有一个值
//            Map<String, Object> map = list.get(0);
//            Long num = (Long) map.get("num");
            // 将上面两行代码合成一行
            Long num = (Long)  list.get(0).get("num");
            if(num == 0){ // 没有重复的记录
                db.update(sql, new Object[]{cname,batch,expiretime,purchaseprice,price,inventory,uid});
            }else{ // 如果名片存在，抛出异常
                throw new DuplicateCardException("商品已经存在！");
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public Commodity showById(String id) throws Exception{
        List<Commodity> list = null;
        DbUtil4 db = null;
        boolean t = false;
        String sql = "SELECT id,cname,batch,expiretime,purchaseprice,price,inventory,create_time,modify_time,isdel,uid FROM t_commodity WHERE id = ? and isdel = 0";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, new Object[]{ id }, Commodity.class);
            if(list!=null && !list.isEmpty()){
                 Commodity commodity = list.get(0);
                return commodity;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
        return null;
    }
    public int update(String cname, String batch, String purchaseprice,String price,String inventory, int id) throws Exception {
        DbUtil4 db = null;
        String sql = "update t_commodity set cname = ?, batch = ?, purchaseprice = ? ,price = ?,inventory = ? where id = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int i = db.update(sql, new Object[]{cname,batch,purchaseprice,price,inventory,id});
            return i;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public int delete(int id) throws Exception {
        DbUtil4 db = null;
        String sql = "UPDATE t_commodity SET isdel = 1 WHERE id = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int j = db.update(sql, new Object[]{id});
            return j;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }

}
